﻿using System;
using System.Collections.Generic;
using System.Collections;
//using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DBConnectionLib;
//using DBConnectionLib.Types;
using Microsoft.SqlServer.Management.Smo; // 2005
using Microsoft.SqlServer.Management.Sdk.Sfc; // 2008

namespace DBSourceToolsLib.SysObjects
{
    public class SysObjects //: Interfaces.IProgressEvent
    {

        public event EventHandler<ProgressEventArgs> ProgressEventPublisher;
        private Commands.DatabaseCommandTypes m_DBCommandType;
        private bool m_HasErrors = false;
        private string m_ErrorMessage = null;
//        public DatabaseScriptOptions ScriptOptions = null;

        private string m_SourceBaseDirectory = "";
        private SourceDatabase m_Database;

        public DatabaseEx DatabaseEx
        {
            get { return m_Database; }
        }
        
        private DBSourceToolsLib.DataSets.ScriptOptionsDS m_TableScriptOptionsDS
            = new DBSourceToolsLib.DataSets.ScriptOptionsDS();
        private DBSourceToolsLib.DataSets.ScriptOptionsDS m_ProcScriptOptionsDS
            = new DBSourceToolsLib.DataSets.ScriptOptionsDS();

        private SortedList<string, SysObject> m_SysObjectsList = new SortedList<string, SysObject>();
        private SODependencyTree m_SODependencyTree;// = new SODependencyTree();

        #region getters and setters
        //public DatabaseListEx DeploymentTargets
        //{
        //    get { return m_DeploymentTargets; }
        //    set { m_DeploymentTargets = value; }
        //}

        public string SourceBaseDirectory
        {
            get { return m_SourceBaseDirectory; }
        }

        public SODependencyTree SODependencyTree
        {
            get { return m_SODependencyTree; }
            set { m_SODependencyTree = value; }
        }
        public SortedList<string, SysObject> SysObjectsList
        {
            get { return m_SysObjectsList; }
            set { m_SysObjectsList = value; }
        }
        public DBSourceToolsLib.DataSets.ScriptOptionsDS TableScriptOptionsDS
        {
            get { return m_TableScriptOptionsDS; }
            set { m_TableScriptOptionsDS = value; }
        }
        public DBSourceToolsLib.DataSets.ScriptOptionsDS ProcScriptOptionsDS
        {
            get { return m_ProcScriptOptionsDS; }
            set { m_ProcScriptOptionsDS = value; }
        }
        public string DatabaseNickName
        {
            get
            {
                return m_Database.NickName;
            }
        }
        public string DatabaseName
        {
            get { return m_Database.DatabaseName; }
        }

        public string DatabaseUserName
        {
            get { return m_Database.UserName; }
        }
        public string DatabasePassword
        {
            get { return m_Database.Password; }
        }
        public string DataSourceName
        {
            get { return m_Database.DataSourceName; }
        }

        #endregion 
        public int Count(SysObjectType oType)
        {
            // only here cause .NET 2.0 doesnt implement the Count property on IEnumerable
            int i = 0;
            switch (oType)
            {
                case SysObjectType.DataType:
                    foreach (SysObject oDataType in this.DataTypes)
                        i++;
                    break;
                case SysObjectType.Function:
                    foreach (SysObject oDataType in this.Functions)
                        i++;
                    break;

                case SysObjectType.Proc:
                    foreach (SysObject oDataType in this.Procs)
                        i++;
                    break;

                case SysObjectType.Querie:
                    foreach (SysObject oDataType in this.Queries)
                        i++;
                    break;

                case SysObjectType.Schema:
                    foreach (SysObject oDataType in this.Schemas)
                        i++;
                    break;

                case SysObjectType.Table:
                    foreach (SysObject oDataType in this.Tables)
                        i++;
                    break;

                case SysObjectType.View:
                    foreach (SysObject oDataType in this.Views)
                        i++;
                    break;

                case SysObjectType.XmlSchema:
                    foreach (SysObject oDataType in this.XmlSchemas)
                        i++;
                    break;
                case SysObjectType.User:
                    foreach (SysObject oUser in this.Users)
                        i++;
                    break;
                case SysObjectType.Role:
                    foreach (SysObject oRole in this.Roles)
                        i++;
                    break;
                case SysObjectType.TableType:
                    foreach (SysObject oTableType in this.TableTypes)
                        i++;
                    break;
                case SysObjectType.Trigger:
                    foreach (SysObject oTrigger in this.Triggers)
                        i++;
                    break;
                case SysObjectType.Synonym:
                    foreach (SysObject oTrigger in this.Synonyms)
                        i++;
                    break;


            }

            return i;
        }

        public SysObjects(SourceDatabase oDatabase, string strBaseDirectory)
        {
            
//            this.m_Database = oDatabase;
            this.m_SourceBaseDirectory = strBaseDirectory;
            this.m_Database = oDatabase;
            this.m_SODependencyTree = new SODependencyTree(this.m_Database, this);
//            this.m_DeploymentTargets = new DatabaseListEx(this.m_SourceBaseDirectory + "\\DeploymentTargets");
//            this.ScriptOptions = new DatabaseScriptOptions(this.m_Database);

        }

        #region Enumerators
        public IEnumerable<SOTable> Tables
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.Table)
                        yield return (SOTable)oSysObject;
                }
            }
        }

        public IEnumerable<SOTableType> TableTypes
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.TableType)
                        yield return (SOTableType)oSysObject;
                }
            }
        }

        public IEnumerable<SODataType> DataTypes
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.DataType)
                        yield return (SODataType)oSysObject;
                }
            }
        }

        public IEnumerable<SOSchema> Schemas
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.Schema)
                        yield return (SOSchema)oSysObject;
                }
            }
        }

        public IEnumerable<SOXmlSchema> XmlSchemas
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.XmlSchema)
                        yield return (SOXmlSchema)oSysObject;
                }
            }
        }

        public IEnumerable<SOView> Views
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.View)
                        yield return (SOView)oSysObject;
                }
            }
        }
        public IEnumerable<SOFunction> Functions
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.Function)
                        yield return (SOFunction)oSysObject;
                }
            }
        }
        public IEnumerable<SOProc> Procs
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.Proc)
                        yield return (SOProc)oSysObject;
                }
            }
        }

        public IEnumerable<SORole> Roles
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.Role)
                        yield return (SORole)oSysObject;
                }
            }
        }

        public IEnumerable<SOQuery> Queries
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.Querie)
                        yield return (SOQuery)oSysObject;
                }
            }
        }

        public IEnumerable<SOUser> Users
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.User)
                        yield return (SOUser)oSysObject;
                }
            }
        }

        public IEnumerable<SOTrigger> Triggers
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.Trigger)
                        yield return (SOTrigger)oSysObject;
                }
            }
        }
        public IEnumerable<SOSynonym> Synonyms
        {
            get
            {
                foreach (SysObject oSysObject in this.SysObjectsList.Values)
                {
                    if (oSysObject.Type == SysObjectType.Synonym)
                        yield return (SOSynonym)oSysObject;
                }
            }
        }

        #endregion

        public void LoadFromDb(EventHandler<ProgressEventArgs> localHandler)
        {
            Clear();
//            EventHandler<ProgressEventArgs> localHandler = ProgressEventPublisher;
            try
            {
                #region sql_query way
                //using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(this.m_Database.ConnectionString))
                //{
                //    conn.Open();
                //    DataSets.SysObjectsDS oSysObjectsDs = new DBSourceToolsLib.DataSets.SysObjectsDS();
                //    DataSets.SysObjectsDSTableAdapters.SysObjectsTableAdapter soTableAdapter = new DBSourceToolsLib.DataSets.SysObjectsDSTableAdapters.SysObjectsTableAdapter();
                //    soTableAdapter.Connection = conn;

                //    soTableAdapter.Fill(oSysObjectsDs.SysObjects);
                //    SysObjectsFactory oSysObjectsFactory = new SysObjectsFactory();

                //    int iTotalCount = oSysObjectsDs.SysObjects.Rows.Count;
                //    int iProcessed = 0;
                //    foreach (DataSets.SysObjectsDS.SysObjectsRow row in oSysObjectsDs.SysObjects)
                //    {
                //        if (localHandler != null)
                //            localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                //                "Processing " + row.name));

                //        SysObject newSysObject = oSysObjectsFactory.getSysObject(row.name, row.type
                //            , this.m_SourceBaseDirectory, this.m_Database);

                //        System.Console.WriteLine("Adding " + newSysObject.QualifiedName);
                //        this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                //    }


                //    if (localHandler != null)
                //        localHandler(this, new ProgressEventArgs(iTotalCount, iTotalCount,
                //            "Processing Complete"));

                //    conn.Close();
                //}
                #endregion


                {
                    //srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
                    Microsoft.SqlServer.Management.Smo.Server oServer = this.m_Database.GetSmoServer();
                    
                    Microsoft.SqlServer.Management.Smo.Database oMDatabase = this.m_Database.GetSmoDatabase(oServer);

                    oServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");

                    //SysObjectsFactory oSysObjectsFactory = new SysObjectsFactory();
                    int iTotalCount = oMDatabase.UserDefinedDataTypes.Count;
                    int iProcessed = 0;

                    foreach (Microsoft.SqlServer.Management.Smo.UserDefinedDataType oSmo in oMDatabase.UserDefinedDataTypes)
                    {

                        if (localHandler != null)
                            localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                "Processing DataType " + oSmo.Name));

                        SODataType newSODataType = new SODataType(oSmo.Name, this.m_SourceBaseDirectory, this.m_Database
                            ,oSmo.Urn.ToString());
                        newSODataType.LoadScript(this.m_Database, newSODataType.FileName, oSmo);
                        this.m_SysObjectsList.Add(newSODataType.QualifiedName, newSODataType);
                    }

                    iTotalCount = oMDatabase.Tables.Count;
                    iProcessed = 0;

                    foreach (Microsoft.SqlServer.Management.Smo.Table oSmo in oMDatabase.Tables)
                    {
                        string strFullName = "";
                        if (oSmo.Schema.CompareTo("dbo") != 0)
                        {
                            strFullName = oSmo.Schema + "." + oSmo.Name;
                        }
                        else
                        {
                            strFullName = oSmo.Name;
                        }

                        if (localHandler != null)
                            localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                "Processing Table " + strFullName));

                        if (!oSmo.IsSystemObject)
                        {

                            SOTable newSODataType = new SOTable(strFullName, this.m_SourceBaseDirectory, this.m_Database,
                                oSmo.Urn.ToString());
                            newSODataType.LoadScript(this.m_Database, newSODataType.FileName, oSmo);
                            
                            this.m_SysObjectsList.Add(newSODataType.QualifiedName, newSODataType);
                        }
                    }

                    iTotalCount = oMDatabase.StoredProcedures.Count;
                    iProcessed = 0;

                    foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure oSmo in oMDatabase.StoredProcedures)
                    {
                        string strFullName = "";
                        if (oSmo.Schema.CompareTo("dbo") != 0)
                        {
                            strFullName = oSmo.Schema + "." + oSmo.Name;
                        }
                        else
                        {
                            strFullName = oSmo.Name;
                        }

                        if (localHandler != null)
                            localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                "Processing Stored Procedure " + strFullName));

                        if (!oSmo.IsSystemObject)
                        {

                            SOProc newSODataType = new SOProc(strFullName, this.m_SourceBaseDirectory, this.m_Database,
                                oSmo.Urn.ToString());
                            newSODataType.LoadScript(this.m_Database, newSODataType.FileName, oSmo);
                            this.m_SysObjectsList.Add(newSODataType.QualifiedName, newSODataType);
                        }
                    }

                    iTotalCount = oMDatabase.Views.Count;
                    iProcessed = 0;

                    foreach (Microsoft.SqlServer.Management.Smo.View oSmo in oMDatabase.Views)
                    {
                        string strFullName = "";
                        if (oSmo.Schema.CompareTo("dbo") != 0)
                        {
                            strFullName = oSmo.Schema + "." + oSmo.Name;
                        }
                        else
                        {
                            strFullName = oSmo.Name;
                        }

                        if (localHandler != null)
                            localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                "Processing View " + strFullName));

                        if (!oSmo.IsSystemObject)
                        {

                            SOView newSODataType = new SOView(strFullName, this.m_SourceBaseDirectory, this.m_Database
                                ,oSmo.Urn.ToString());
                            newSODataType.LoadScript(this.m_Database, newSODataType.FileName, oSmo);
                            this.m_SysObjectsList.Add(newSODataType.QualifiedName, newSODataType);
                        }
                    }

                    foreach (Microsoft.SqlServer.Management.Smo.Synonym oSmo in oMDatabase.Synonyms)
                    {

                        string strFullName = "";
                        if (oSmo.Schema.CompareTo("dbo") != 0)
                            strFullName = oSmo.Schema + "." + oSmo.Name;
                        else
                            strFullName = oSmo.Name;

                        if (localHandler != null)
                            localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                "Processing Synonym " + strFullName));

                        //SOSynonym

                        SOSynonym newSODataType = new SOSynonym(strFullName, this.m_SourceBaseDirectory, this.m_Database
                            , oSmo.Urn.ToString());
                        newSODataType.LoadScript(this.m_Database, newSODataType.FileName, oSmo);
                        this.m_SysObjectsList.Add(newSODataType.QualifiedName, newSODataType);

                    }

                    iTotalCount = oMDatabase.UserDefinedFunctions.Count;
                    iProcessed = 0;

                    foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction oSmo in oMDatabase.UserDefinedFunctions)
                    {
                        if (localHandler != null)
                            localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                "Processing Function " + oSmo.Name));

                        if (!oSmo.IsSystemObject)
                        {

                            SOFunction newSODataType = new SOFunction(oSmo.Schema + "." + oSmo.Name, this.m_SourceBaseDirectory, this.m_Database, 
                                oSmo.Urn.ToString());
                            newSODataType.LoadScript(this.m_Database, newSODataType.FileName, oSmo);
                            this.m_SysObjectsList.Add(newSODataType.QualifiedName, newSODataType);
                        }
                    }

                    try
                    {
                        iTotalCount = oMDatabase.Schemas.Count;
                        iProcessed = 0;
                        foreach (Microsoft.SqlServer.Management.Smo.Schema oSmo in oMDatabase.Schemas)
                        {
                            if (localHandler != null)
                                localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                    "Processing Schema " + oSmo.Name));
                            if ((oSmo.Owner.CompareTo("dbo") == 0)
                                && oSmo.Name.CompareTo("dbo") != 0)
                            {
                                SOSchema newSODataType = new SOSchema(oSmo.Name, this.m_SourceBaseDirectory, this.m_Database
                                    ,oSmo.Urn.ToString());
                                newSODataType.LoadScript(this.m_Database, newSODataType.FileName, oSmo);
                                this.m_SysObjectsList.Add(newSODataType.QualifiedName, newSODataType);
                            }

                        }
                        iTotalCount = oMDatabase.XmlSchemaCollections.Count;
                        iProcessed = 0;
                        foreach (Microsoft.SqlServer.Management.Smo.XmlSchemaCollection oSmo in oMDatabase.XmlSchemaCollections)
                        {
                            if (localHandler != null)
                                localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                    "Processing Schema " + oSmo.Name));
                            //if ((oSmo.Owner.CompareTo("dbo") == 0)
                            //    && oSmo.Name.CompareTo("dbo") != 0)
                            //{
                            SOXmlSchema newSODataType = new SOXmlSchema(oSmo.Schema + "." + oSmo.Name, this.m_SourceBaseDirectory, this.m_Database
                                ,oSmo.Urn.ToString());
                            newSODataType.LoadScript(this.m_Database, newSODataType.FileName, oSmo);
                            this.m_SysObjectsList.Add(newSODataType.QualifiedName, newSODataType);
                            //                        }

                        }

                        iTotalCount = oMDatabase.Roles.Count;
                        iProcessed = 0;
                        foreach (Microsoft.SqlServer.Management.Smo.DatabaseRole oRole in oMDatabase.Roles)
                        {
                            
                            if (localHandler != null)
                                localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                    "Processing Role " + oRole.Name));

                            if (!oRole.IsFixedRole)
                            {
                                if (oRole.Name.CompareTo("public") != 0) // there is a bug with this role
                                {
                                    SORole newUser = new SORole(oRole.Name, this.m_SourceBaseDirectory, this.m_Database
                                        , oRole.Urn.ToString());
                                    newUser.LoadScript(this.m_Database, newUser.FileName, oRole);
                                    this.m_SysObjectsList.Add(newUser.QualifiedName, newUser);
                                }
                            }

                        }

                        iTotalCount = oMDatabase.Users.Count;
                        iProcessed = 0;
                        foreach (Microsoft.SqlServer.Management.Smo.User oUser in oMDatabase.Users)
                        {
                            if (localHandler != null)
                                localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                    "Processing User " + oUser.Name));

                            if (!oUser.IsSystemObject)
                            {
                                SOUser newUser = new SOUser(oUser.Name, this.m_SourceBaseDirectory, this.m_Database
                                    , oUser.Urn.ToString());
                                newUser.LoadScript(this.m_Database, newUser.FileName, oUser);
                                this.m_SysObjectsList.Add(newUser.QualifiedName, newUser);
                            }
                        }

                        //oMDatabase.UserDefinedDataTypes.
                        int iVer = oServer.VersionMajor;
                        
                        // sql 2008 only
                        if (oServer.VersionMajor > 9)
                        {
                            iTotalCount = oMDatabase.UserDefinedTableTypes.Count;
                            iProcessed = 0;

                            foreach (Microsoft.SqlServer.Management.Smo.UserDefinedTableType oTableType in oMDatabase.UserDefinedTableTypes)
                            {
                                if (localHandler != null)
                                    localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                        "Processing TableType " + oTableType.Name));

                                SOTableType newTableType = new SOTableType(oTableType.Name, this.m_SourceBaseDirectory, this.m_Database
                                    , oTableType.Urn.ToString());
                                newTableType.LoadScript(this.m_Database, newTableType.FileName, oTableType);
                                this.m_SysObjectsList.Add(newTableType.QualifiedName, newTableType);

                            }
                        }

                        // triggers
                        iTotalCount = oMDatabase.Triggers.Count;
                        iProcessed = 0;
                        foreach (Microsoft.SqlServer.Management.Smo.DatabaseDdlTrigger oTrigger in oMDatabase.Triggers)
                        {
                            if (localHandler != null)
                                localHandler(this, new ProgressEventArgs(iProcessed++, iTotalCount,
                                    "Processing Trigger " + oTrigger.Name));

                            if (!oTrigger.IsSystemObject)
                            {
                                SOTrigger newTrigger = new SOTrigger(oTrigger.Name, this.m_SourceBaseDirectory, this.m_Database
                                    , oTrigger.Urn.ToString());
                                newTrigger.LoadScript(this.m_Database, newTrigger.FileName, oTrigger);
                                this.m_SysObjectsList.Add(newTrigger.QualifiedName, newTrigger);
                            }
                        }


                    }
                    

                    catch (InvalidVersionEnumeratorException ex)
                    {
                        System.Console.WriteLine(ex.ToString());
                    }



                }

                this.LoadQueriesFromDisk();

                #region smo way
                // generates 1272 stored procedures, seems to be stuck in a loop.

                //iTotalCount = oMDatabase.Views.Count;
                //iProcessed = 0;

                //srv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");

                //foreach (Microsoft.SqlServer.Management.Smo.View oView in oMDatabase.Views)
                //{
                //    if (!oView.IsSystemObject)
                //    {
                //        if (localHandler != null)
                //            localHandler(this, new ProgressEventArgs(iProcessed, iTotalCount,
                //                "Processing View " + oView.Name));

                //        SOView newSoView = new SOView(oView.Name, this.m_SourceBaseDirectory, oDatabase);
                //        m_SysObjectsList.Add(newSoView.QualifiedName, newSoView);
                //    }
                //    iProcessed++;
                //}
                //iTotalCount = oMDatabase.UserDefinedFunctions.Count;
                //iProcessed = 0;

                //foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction oFunction in oMDatabase.UserDefinedFunctions)
                //{
                //    if (!oFunction.IsSystemObject)
                //    {
                //        if (localHandler != null)
                //            localHandler(this, new ProgressEventArgs(iProcessed, iTotalCount,
                //                "Processing Function " + oFunction.Name));

                //        SOFunction newSoFunction = new SOFunction(oFunction.Name, this.m_SourceBaseDirectory, oDatabase);
                //        m_SysObjectsList.Add(newSoFunction.QualifiedName, newSoFunction);
                //    }
                //    iProcessed++;
                //}

                #endregion

            }
            catch (Microsoft.SqlServer.Management.Smo.FailedOperationException ex)
            {
                // do not re-throw a thread abort exception
                if (localHandler != null)
                    localHandler(this, new ProgressEventArgs(-1, -1,
                                ex.ToString()));
                System.Console.WriteLine(ex.ToString());
                //throw new Exception(ex.ToString());
            }
            catch (Exception ex)
            {

                System.Console.WriteLine(ex.ToString());
                if (localHandler != null)
                    localHandler(this, new ProgressEventArgs(-1, -1,
                                ex.ToString()));

                //throw new Exception(ex.ToString());
            }
        }



        private void Clear()
        {
            this.m_SysObjectsList.Clear();

        }

        //public void aWriteCreateScript(DatabaseEx parentDatabase)
        //{
        //    if (this.m_Database.IsDeploymentTarget)
        //        this.SODependencyTree.aWriteCreateScripts(parentDatabase
        //            ,""//,parentDatabase.SysObjects.DeploymentTargets.DatabaseListXMLFile
        //            ,""//this.ScriptOptions.BinDir
        //            );
        //}

        public void WriteFiles(bool bWriteData, bool isDeploymentTarget)
        {
            EventHandler<ProgressEventArgs> localHandler = ProgressEventPublisher;
            int count = 0;
            int TotalCount = this.SysObjectsList.Values.Count;

            if (isDeploymentTarget)
            {
                this.m_Database.DropScript.WriteScript();
                this.m_Database.CreateScript.WriteScript();
            }

            foreach (SysObject sysObject in this.SysObjectsList.Values)
            {
                if (localHandler != null)
                    localHandler(this, new ProgressEventArgs(count, TotalCount,
                        "Processing " + sysObject.Name));

                if (this.m_Database.IsDeploymentTarget)
                {
                    switch (sysObject.Type)
                    {
                        case SysObjectType.Table:
                            {
                                //if (this.ScriptOptions.MustScriptTable(sysObject.Name))
                                //    sysObject.WriteFile(this.ScriptOptions.MustScriptData(sysObject.Name), isDeploymentTarget,
                                //        this.ScriptOptions.OrderByField(sysObject.Name));
                            }
                            break;
                        case SysObjectType.View:
                            {
                                //if (this.ScriptOptions.MustScriptView(sysObject.Name))
                                //    sysObject.WriteFile(false, isDeploymentTarget, null);
                            }
                            break;
                        default:
                            sysObject.WriteFile(false, isDeploymentTarget, null, false);
                            break;
                    }
                }
                else
                {
                    if (sysObject.Type == SysObjectType.Table)
                    {
                        //sysObject.WriteFile(this.ScriptOptions.ScriptData && this.ScriptOptions.MustScriptData(sysObject.Name), isDeploymentTarget,
                        //    this.ScriptOptions.OrderByField(sysObject.Name));
                    }
                    else
                    {
                        sysObject.WriteFile(false, isDeploymentTarget, null, false);
                    }

//                    sysObject.WriteFile(false, isDeploymentTarget);
                }
                count++;
            }
        }

        internal void WriteFiles(DatabaseScriptOptions databaseScriptOptions
            , EventHandler<ProgressEventArgs> localHandler)
        {
                int count = 0;
                int TotalCount = this.SysObjectsList.Values.Count;

                foreach (SysObject sysObject in this.SysObjectsList.Values)
                {
                    if (localHandler != null)
                        localHandler(this, new ProgressEventArgs(count, TotalCount,
                            "Processing " + sysObject.Name));

                    if (sysObject.Type == SysObjectType.Table)
                    {
                        sysObject.WriteFile(databaseScriptOptions.ScriptData && databaseScriptOptions.MustScriptData(sysObject.Name), false,
                            databaseScriptOptions.OrderByField(sysObject.Name), databaseScriptOptions.RefreshData);
                    }
                    else
                    {
                        sysObject.WriteFile(false, false, null, false);
                    }

                    //                    sysObject.WriteFile(false, isDeploymentTarget);
                    count++;
                }
        }



        public void LoadFromDisk()
        {
            this.Clear();
            //throw new NotImplementedException();
            SysObjectsFactory oSysObjectsFactory = new SysObjectsFactory();
            string strFileDirectory = this.m_SourceBaseDirectory + "\\Tables";

            this.LoadQueriesFromDisk();

            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.Table, m_Database);

                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }
            strFileDirectory = this.m_SourceBaseDirectory + "\\Procs";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.Proc, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }
            strFileDirectory = this.m_SourceBaseDirectory + "\\Functions";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.Function, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }
            strFileDirectory = this.m_SourceBaseDirectory + "\\Views";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.View, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }
            strFileDirectory = this.m_SourceBaseDirectory + "\\DataTypes";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.DataType, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }
            strFileDirectory = this.m_SourceBaseDirectory + "\\Schemas";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.Schema, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }
            strFileDirectory = this.m_SourceBaseDirectory + "\\XmlSchemas";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.XmlSchema, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }
            strFileDirectory = this.m_SourceBaseDirectory + "\\Users";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.User, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }

            strFileDirectory = this.m_SourceBaseDirectory + "\\Roles";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.Role, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }

            strFileDirectory = this.m_SourceBaseDirectory + "\\TableTypes";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.TableType, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }

            strFileDirectory = this.m_SourceBaseDirectory + "\\Triggers";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.Trigger, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }

            strFileDirectory = this.m_SourceBaseDirectory + "\\Synonyms";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.Synonym, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }

        }

        private void LoadQueriesFromDisk()
        {
            SysObjectsFactory oSysObjectsFactory = new SysObjectsFactory();
            string strFileDirectory = this.m_SourceBaseDirectory + "\\Queries";
            if (System.IO.Directory.Exists(strFileDirectory))
            {
                foreach (string strFileName in System.IO.Directory.GetFiles(strFileDirectory, "*.sql"))
                {
                    SysObject newSysObject = oSysObjectsFactory.getSysObject
                        (strFileName, SysObjectType.Querie, m_Database);
                    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
                }
            }

        }

        public SysObject getSysObject(string strTypeName, string strObjectName)
        {
            // remove the 's' 
            string key = strTypeName.Substring(0, strTypeName.Length - 1) + "." + strObjectName;
            if (this.m_SysObjectsList.ContainsKey(key))
            {
                return this.m_SysObjectsList[key];
            }
            return null;

        }

        #region IProgressEvent Members

        public void Execute()
        {
            // replaced by SourceDatabaseLoader
            EventHandler<ProgressEventArgs> localHandler = ProgressEventPublisher;

            switch (this.m_DBCommandType)
            {
                //case DBSourceToolsLib.Commands.DatabaseCommandTypes.LoadTableList:
                //    this.m_TableScriptOptionsDS = this.ScriptOptions.LoadTablesFromDB(this, localHandler);
                //    break;
                //case DBSourceToolsLib.Commands.DatabaseCommandTypes.LoadStoredProcList:
                //    this.m_ProcScriptOptionsDS = this.ScriptOptions.LoadStoredProcsFromDB(this, localHandler);
                //    break;
                //case DBSourceToolsLib.Commands.DatabaseCommandTypes.LoadViewList:
                //    this.m_ProcScriptOptionsDS = this.ScriptOptions.LoadViewsFromDB(this, localHandler);
                //    break;
                //case DBSourceToolsLib.Commands.DatabaseCommandTypes.LoadUserList:
                //    this.m_ProcScriptOptionsDS = this.ScriptOptions.LoadUsersFromDB(this, localHandler);
                //    break;
            }

            if (localHandler != null)
                localHandler(this, new ProgressEventArgs(100, 100,
                    "Processing Complete"));
        }



        #endregion

        //public void SetDatabase(DatabaseEx database)
        //{
        //    this.m_Database = database;
        //    this.m_SODependencyTree = new SODependencyTree(this.m_Database, this);
        //    this.m_DeploymentTargets = new DatabaseListEx(this.m_SourceBaseDirectory + "\\DeploymentTargets");
        //}

        public void SetDBCommandType(Commands.DatabaseCommandTypes dbCommandType)
        {
            this.m_DBCommandType = dbCommandType;
        }

        //public string NewQueryName()
        //{
        //    int i = 0;
        //    foreach (SOQuery oQuery in this.Queries)
        //        i++;
        //    string strKeyName = "Query" + (i + 1).ToString();
        //    if (m_SysObjectsList.ContainsKey("Querie." + strKeyName))
        //    {
        //        // a query with this name already exists.
        //        strKeyName += "_1";
        //    }
        //    return strKeyName;
        //}

        //internal void NewQuery()
        //{
        //    string strNewQueryName = NewQueryName() + ".sql";
        //    SysObjectsFactory oSysObjectsFactory = new SysObjectsFactory();
        //    SysObject newSysObject = oSysObjectsFactory.getSysObject
        //        (this.m_SourceBaseDirectory + "\\Queries\\" + strNewQueryName, SysObjectType.Querie, m_Database);
        //    this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);

        //}

        public void RenameSysObject(string strType, string strOldName, string strNewName)
        {
            SysObject oldSysObject = getSysObject(strType, strOldName);
            if (oldSysObject == null)
                throw new Exception("SysObjects does not contain object " + strType + "." + strOldName);

            SysObjectsFactory oFactory = new SysObjectsFactory();
            SysObject newSysObject = oFactory.NewSysObject(strNewName, oldSysObject, this.m_Database);
            newSysObject.Name = strNewName;
            newSysObject.Text = oldSysObject.Text;

            this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
            this.m_SysObjectsList.Remove(oldSysObject.QualifiedName);

        }

        public void LoadDependencyTreeFromDb(EventHandler<ProgressEventArgs> localHandler)
        {
            this.SODependencyTree.LoadFromDatabase(localHandler);
        }

        void oWalker_DiscoveryProgress(object sender, ProgressReportEventArgs e)
        {
            System.Console.WriteLine(e.Current);
        }

        public void LoadDependencyTreeFromDisk()
        {
            this.SODependencyTree.LoadFromDisk();
        }

        public void WriteDependencyTree()
        {
            this.SODependencyTree.Write();
        }

        //public void LoadDeploymentTargets(string strBaseDirectory)
        //{
        //    //throw new Exception("change LoadDatabases()");
        //    this.m_DeploymentTargets.LoadDeploymentTargets(strBaseDirectory + @"\DeploymentTargets", "", false, true,
        //        this.m_Database);
        //}

//        public void WriteDeploymentTargets()
//        {
//            foreach (DatabaseEx oDatabase in this.m_DeploymentTargets.Databases)
//            {
//                //DirHelper oDirHelper = new DirHelper();
//                //oDirHelper.DeleteFilesInDir(oDatabase.BaseDirectory);
//                oDatabase.IsDeploymentTarget = true;

//                //oDatabase.SysObjects = new SysObjects(oDatabase, oDatabase.BaseDirectory);

//                oDatabase.SysObjects.Clear();
//                oDatabase.SysObjects.Clone(this, oDatabase, this.m_SODependencyTree);

//                //oDatabase.SysObjects.ScriptOptions = this.ScriptOptions;

//                oDatabase.SysObjects.WriteCreateScript(this.m_Database);
//            }
////            throw new NotImplementedException();
//        }

        //private void Clone(SysObjects sourceSysObjects, DatabaseEx targetDatabase, SODependencyTree sourceSODependencyTree)
        //{
        //    //clone actual sys objects
        //    SysObjectsFactory oSysObjectsFactory = new SysObjectsFactory();
        //    foreach (SysObject oSysObject in sourceSysObjects.SysObjectsList.Values)
        //    {
        //        SysObject newSysObject = oSysObjectsFactory.CloneSysObject(oSysObject, targetDatabase);
        //        this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
        //    }
        //    this.m_SODependencyTree.Clone(sourceSODependencyTree);
        //}

        //public void AddDeploymentTarget(DBConnectionLib.Database database)
        //{
        //    database.BaseDirectory = this.m_Database.BaseDirectory + "\\DeploymentTargets\\" + database.NickName;
        //    DatabaseEx oNewTarget = new DatabaseEx(database, true);
        //    this.m_DeploymentTargets.AddDatabase(oNewTarget);
        //    oNewTarget.AddDropAndCreateScript(this.m_Database);

        //}

        //public void ClearDeploymentTargets()
        //{
        //    this.m_DeploymentTargets.ClearDatabases();
        //}

        //internal void DeployTarget(DBConnectionLib.Database oDeploymentTargetDatabase, System.Windows.Forms.Form form)
        //{
        //    throw new NotImplementedException();
        //}






        internal string NewQuery(DBSourceToolsLib.Commands.DatabaseCommandTypes m_CommandType, string m_Params)
        {
            // TODO : COMPLETE 1.2.0.8 Write new query do disk, then reload.
            string strCurrentQueryName = this.m_SourceBaseDirectory + "\\Queries\\Query.sql";
            FileUtils oFileUtils = new FileUtils();
            string strNewQueryFileName = oFileUtils.NewFile(strCurrentQueryName);
            
            SysObjectsFactory oSysObjectsFactory = new SysObjectsFactory();
            SysObject newSysObject = oSysObjectsFactory.getSysObject
                (strNewQueryFileName, SysObjectType.Querie, m_Database);

            QueryGenerator oGenerator = new QueryGenerator();
            string strQueryText = "";
            switch (m_CommandType)
            {
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewProc:
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.CountDataQuery:
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewTableQuery:
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewFunction:
                
                    strQueryText  = oGenerator.GenerateQuery(this.m_Database, m_CommandType);
                    break;
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewDatabase:
                case Commands.DatabaseCommandTypes.NewQueryScriptInsert:
                case Commands.DatabaseCommandTypes.NewQueryScriptSelect:
                case Commands.DatabaseCommandTypes.NewView:
                case Commands.DatabaseCommandTypes.NewQuerySelectView:
                case Commands.DatabaseCommandTypes.NewQueryUpdateTable:
                    strQueryText = oGenerator.GenerateQuery(m_Database, m_CommandType, m_Params);
                    break;
                case DBSourceToolsLib.Commands.DatabaseCommandTypes.NewTableFromExcel:
                    {
                        string strText = oGenerator.GenerateQuery(m_Database, m_CommandType, m_Params);
                        DBSourceToolsLib.DatabaseProjectUI.NewTableNameForm oForm 
                            = new DBSourceToolsLib.DatabaseProjectUI.NewTableNameForm();

                        if (oForm.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                        {
                            strText = strText.Replace("[/*[new_table_name]*/]", oForm.NewTableName);
                        }
                        strQueryText = strText;
                    }
                    break;
                
            }

            newSysObject.Text = strQueryText.Replace('\r', ' ');
            newSysObject.WriteFile();
            this.m_SysObjectsList.Add(newSysObject.QualifiedName, newSysObject);
            return strNewQueryFileName;

        }

        //private string NewQueryFileName()
        //{
        //    return NewQueryName() + ".sql";
        //}

        #region IProgressEvent Members


        public bool HasErrors()
        {
            return m_HasErrors;
        }

        public string GetErrorMessage()
        {
            return m_ErrorMessage;
        }

        #endregion

        internal void ReLoadQueriesFromDisk()
        {
            List<string> oListKeys = new List<string>();
            foreach (string strKey in m_SysObjectsList.Keys)
            {
                SysObject oSysObject = m_SysObjectsList[strKey];
                if (oSysObject is SOQuery)
                    oListKeys.Add(strKey);
            }

            foreach (string strQueryKey in oListKeys)
                m_SysObjectsList.Remove(strQueryKey);

            LoadQueriesFromDisk();
            
        }

        public void Delete(string strKey)
        {
            if (m_SysObjectsList.ContainsKey(strKey))
                m_SysObjectsList.Remove(strKey);
            //SysObject oFoundSysObject = getSysObject(strType, strObject);
            //if (oFoundSysObject != null)
            //    m_SysObjectsList.Remove(oFoundSysObject.QualifiedName);
        }
    }
}
